IF OBJECT_ID('dbo.RptPCROMCABreakoutSummary') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROMCABreakoutSummary
    IF OBJECT_ID('dbo.RptPCROMCABreakoutSummary') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROMCABreakoutSummary >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROMCABreakoutSummary >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROMCABreakoutSummary
--
-- Author:      Arthur Bianchini
--
-- Create date: 10/30/2009
--
-- Description: 
--                  
--
-- Input Parameters - XML: (no multi-select parameters)
-- Institution(*):
-- Department:
-- Unit:
-- Fund #s (separate by commas):
-- Principal Investigator: 
-- Sponsor:
-- Originating Sponsor:

--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- 
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/30/09    aab        Created
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROMCABreakoutSummary 
     (
     
	 @CSCRCompleteDateFrom		SMALLDATETIME,
	 @CSCRCompleteDateTo			SMALLDATETIME,
	 @Institution									VARCHAR(100) = NULL,
 	 @Department								 VARCHAR(200) = NULL,
	 @Unit											 VARCHAR(250) = NULL,
     @PrincipalInvestigator				VARCHAR(2000) = NULL,    
     @Sponsor									VARCHAR(2000) = NULL,      
	 @OriginatingSponsor                 VARCHAR(2000) = NULL,  
	 @DomainUserId							INT
     )                        
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking

     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
	 IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
	 IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL
	
CREATE TABLE   #Proposal 
								(PropNo varchar(10))

CREATE CLUSTERED INDEX IX_#Proposal_PropNo
    ON #Proposal (PropNo); 

     INSERT INTO #Proposal
     
     SELECT sec.InfoEdPropNumber
     FROM INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec

SELECT
FundType,
FundTypeOrder,
No3rdPartyBilling = SUM(CASE WHEN QualifyingStudyStatus='No 3rd Party Billing' THEN 1 ELSE 0 END),
NonQualifyingGrid = SUM(CASE WHEN QualifyingStudyStatus='Non Qualifying - Grid' THEN 1 ELSE 0 END),
NonQualifyingNoGrid = SUM(CASE WHEN QualifyingStudyStatus='Non Qualifying - No Grid' THEN 1 ELSE 0 END),
Qualifying = SUM(CASE WHEN QualifyingStudyStatus='Qualifying' THEN 1 ELSE 0 END),
Unassigned = SUM(CASE WHEN QualifyingStudyStatus='Unassigned' THEN 1 ELSE 0 END)
FROM
(
SELECT DISTINCT
p.ProposalNumber,
p.PrincipalInvestigator,  
p.Institution, 
p.DepartmentChiefCode,
p.Department,
p.UnitChiefCode,
p.Unit,
p.SponsorName, 
p.OriginatingSponsor,
d.ReportName as DeliverableName,--QA
c.code_desc as ReportCategory,--QA
ds.code_desc as DeliverableStatus,--QA
pds.StatusDate,
CASE 
WHEN u3.p_sin_40 is null THEN 'Unassigned'
WHEN	u3.p_sin_40 = '' THEN 'Unassigned'
ELSE u3.p_sin_40
END as FundType,
CASE 
WHEN u4.p_sin_41 is null THEN 'Unassigned'
WHEN	u4.p_sin_41 = '' THEN 'Unassigned'
ELSE u4.p_sin_41
END as QualifyingStudyStatus,
u5.p_sin_32 as MCAFeeRequired,
p.ProposalTitle,
CASE 
WHEN u3.p_sin_40 = 'Corporate' THEN 1
WHEN u3.p_sin_40 = 'Federal' THEN 2
WHEN u3.p_sin_40 = 'Foundation' THEN 3
WHEN u3.p_sin_40 = 'Unfunded' THEN 4
WHEN u3.p_sin_40 is null THEN 5
WHEN	u3.p_sin_40 = '' THEN 5
END as FundTypeOrder
FROM vwMasterProposalsData p
INNER JOIN #Proposal pf 
	ON p.PropNo = pf.PropNo
LEFT JOIN		prop_u u5--mca fee required
	ON	p.instcode = u5.inst_code
	AND	p.propno = u5.prop_no
LEFT JOIN		prop_u u4--QualifyingStudyStatus
	ON	p.instcode = u4.inst_code
	AND	p.propno = u4.prop_no
LEFT JOIN		prop_u u3--FundType  
	ON	p.instcode = u3.inst_code
	AND	p.propno = u3.prop_no
JOIN		PTDeliverable d
	ON	p.instcode = d.inst_code
	AND	p.propno = d.prop_no
JOIN		PTDeliverableStatus pds
	ON	pds.DeliverableID = d.DeliverableID
	AND	pds.REPORTSTAT = d.REPORTSTAT
	AND	pds.StatusDate = (SELECT MAX(pds1.StatusDate)
										FROM	PTDeliverableStatus pds1
										WHERE	pds.DeliverableID  = pds1.DeliverableID 
										AND	pds.REPORTSTAT = pds1.REPORTSTAT)
JOIN codetab ds
	ON d.reportstat = ds.codeID
	AND	ds.arr_name = 'REPORTSTAT'
	AND	ds.code_desc = 'CSCR - Completed'
JOIN codetab c
	ON d.reportcat = c.codeID
	AND c.arr_name = 'reportcat' 
	AND c.code_desc = 'CSCR - MCA'
 INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
			ON p.ProposalNumber = ai.FolderNumber
WHERE	
	   (pds.StatusDate BETWEEN @CSCRCompleteDateFrom AND @CSCRCompleteDateTo) AND
	   (ai.institutionid= @Institution  OR @Institution IS NULL OR @Institution = 0) AND 
	   (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 ) AND 
	   (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='') AND
       (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0') AND   
	   (@Sponsor IS NULL OR p.SponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @Sponsor ))) AND
       (@OriginatingSponsor IS NULL OR p.OriginatingSponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @OriginatingSponsor )))
) DT
GROUP BY FundType,FundTypeOrder
ORDER BY FundTypeOrder
    
SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
IF @Error!=0
     RETURN -1
      
  -- Return Success
RETURN 0

     
END
GO

IF OBJECT_ID('dbo.RptPCROMCABreakoutSummary') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROMCABreakoutSummary >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROMCABreakoutSummary>>>'
GO

--GRANT EXECUTE ON dbo.RptPCROMCABreakoutSummary TO InsightReport
GO
